﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.IO;
using System.Security.Cryptography;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using CoreShop.App;
using CoreShop.Library;
using CoreShop.Services;
using System.Windows.Forms;
using CoreShop.Model;
using CoreShop.PrintService;
using SqlSugar;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Rebar;
using Yitter.IdGenerator;
using SqlSugar.Extensions;
using MySqlX.XDevAPI.Relational;
using NPOI.SS.Formula.Functions;
using Sunny.UI;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;
using static CoreShop.Library.GlobalEnumVars;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;
using Sunny.UI.Win32;
using gregn6Lib;
using static System.Windows.Forms.AxHost;

namespace CoreShop
{
    public class DataHelper
    {
        protected GridppReport Report = new GridppReport();

        /// <summary>
        /// 获取版本号
        /// </summary>
        /// <param name="newVersion"></param>
        /// <returns></returns>
        public int GetUpgradeVersion()
        {
            int oldVersion = 0;
            var numberDetail = new corecmsnumberdetailService();
            var numberModel = numberDetail.SelectDataById((int)GlobalEnumVars.SerialNumberType.系统版本号);
            if (numberModel != null ) oldVersion = numberModel.lastNumber;
            return oldVersion;
        }

        /// <summary>
        /// 获取版本号
        /// </summary>
        /// <param name="newVersion"></param>
        /// <returns></returns>
        public void SetUpgradeVersion(int sVersion)
        {
            try
            {
                var numberDetail = new corecmsnumberdetailService();
                var numberModel = numberDetail.SelectDataById((int)GlobalEnumVars.SerialNumberType.系统版本号);
                numberModel.lastNumber = sVersion;
                numberDetail.UpdateAsync(numberModel);
            }
            catch (Exception ex) { }
        }

        #region 升级报表格式保存到数据库
        /// <summary>
        /// 自动升级数据库
        /// </summary>
        public void SetDataUpgrade_Report(int sVersion)
        {
            try
            {
                var list = new corecmsprintbilltemplateServices().GetList();
                foreach (var row in list)
                {
                    if (row.reportData.IsNullOrEmpty())
                    {
                        string sFile = System.Windows.Forms.Application.StartupPath + "\\report\\" + row.reportFile;
                        if (File.Exists(sFile))
                        {
                            Report.LoadFromFile(sFile);
                            row.reportData = Report.SaveToStr();
                        }                       
                    }
                }
                new corecmsprintbilltemplateServices().UpdateRangeAsync(list);
            }
            catch { }
        }

        #endregion

        #region 版本250621 旧版本升级
        /// <summary>
        /// 自动升级数据库
        /// </summary>
        public void SetDataUpgrade_250621(int sVersion)
        {
            string msql = "";
            if (!CheckColumn("corecmsfoodspec", "specUnitName"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD specUnitName varchar(50);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodspec", "number"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD number varchar(50);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodspec", "name"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD name varchar(200);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "weighingCodeType"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD weighingCodeType int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "weighingCodeSign"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD weighingCodeSign varchar(50);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodspec", "weighPlu"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD weighPlu int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodspec", "stand"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD stand int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "aiType"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD aiType int;";
                ExecuteCommand(msql);
                msql = "Update corecmssystemsetup Set aiType = 0;";
                ExecuteCommand(msql);
            }

            if (!CheckColumn("corecmssystemsetup", "starWeight"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD starWeight int;";
                ExecuteCommand(msql);
                msql = "Update corecmssystemsetup Set starWeight = 5;";
                ExecuteCommand(msql);
            }

            if (!CheckColumn("corecmssystemsetup", "paraPartial"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD paraPartial text;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "mergeOrderItem"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD mergeOrderItem tinyint;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsexchange", "screemImgId"))
            {
                msql = "ALTER TABLE corecmsexchange ADD screemImgId bigint;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsexchange ADD screemImgTime datetime;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "SecondScreemTimes"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD SecondScreemTime int;";
                ExecuteCommand(msql);
                msql = "Update corecmssystemsetup Set SecondScreemTime = 5;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodspec", "vvipPrice"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD vvipPrice decimal(12, 4);";
                ExecuteCommand(msql);
                msql = "Update corecmsfoodspec Set vvipPrice = 0;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfoodspec ADD commPrice decimal(12, 4);";
                ExecuteCommand(msql);
                msql = "Update corecmsfoodspec Set commPrice = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordering", "unitName"))
            {
                msql = "ALTER TABLE corecmsfoodordering ADD unitName varchar(100);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderdetail", "unitName"))
            {
                msql = "ALTER TABLE corecmsfoodorderdetail ADD unitName varchar(100);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordering", "code"))
            {
                msql = "ALTER TABLE corecmsfoodordering ADD code varchar(50);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderdetail", "code"))
            {
                msql = "ALTER TABLE corecmsfoodorderdetail ADD code varchar(50);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordering", "limitBuyQty"))
            {
                msql = "ALTER TABLE corecmsfoodordering ADD limitBuyQty decimal(10, 2);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderdetail", "limitBuyQty"))
            {
                msql = "ALTER TABLE corecmsfoodorderdetail ADD limitBuyQty decimal(10, 2);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodcategory", "stand"))
            {
                msql = "ALTER TABLE corecmsfoodcategory ADD stand int;";
                ExecuteCommand(msql);
                msql = "Update corecmsfoodcategory Set stand = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordermaster", "sourceNo"))
            {
                msql = "ALTER TABLE corecmsfoodordermaster ADD sourceNo varchar(50);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordermaster", "sourceId"))
            {
                msql = "ALTER TABLE corecmsfoodordermaster ADD sourceId bigint;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderingtag", "ActivitySubType"))
            {
                msql = "ALTER TABLE corecmsfoodorderingtag ADD ActivitySubType int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderingtag", "activityInfoId"))
            {
                msql = "ALTER TABLE corecmsfoodorderingtag ADD activityInfoId bigint;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordertag", "ActivitySubType"))
            {
                msql = "ALTER TABLE corecmsfoodordertag ADD ActivitySubType int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordertag", "activityInfoId"))
            {
                msql = "ALTER TABLE corecmsfoodordertag ADD activityInfoId bigint;";
                ExecuteCommand(msql);
            }
            if (CheckColumn("corecmsfoodorderdetail", "entryNo"))
            {
                msql = "ALTER TABLE corecmsfoodorderdetail DROP entryNo;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderdetail", "rowId"))
            {
                msql = "ALTER TABLE corecmsfoodorderdetail ADD rowId int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsexchange", "employeeId"))
            {
                msql = "ALTER TABLE corecmsexchange ADD employeeId bigint;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsexchange ADD employeeTime datetime;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "employeeSelType"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD employeeSelType int;";
                ExecuteCommand(msql);
                msql = "Update corecmssystemsetup Set employeeSelType = 0;";
                ExecuteCommand(msql);
            }
            SetUpgradeVersion(sVersion);
        }

        /// <summary>
        /// 自动升级数据库
        /// </summary>
        public void SetDataUpgrade_250812(int sVersion)
        {
            string msql = "";
            if (!CheckColumn("corecmsemployee", "isDefault"))
            {
                msql = "ALTER TABLE corecmsemployee ADD isDefault int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderdetail", "allAmount1"))
            {
                msql = "ALTER TABLE corecmsfoodorderdetail ADD allAmount1 decimal(12, 4);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderpayment", "sourceId"))
            {
                msql = "ALTER TABLE corecmsfoodorderpayment ADD sourceId bigint;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsuser", "cardNo"))
            {
                msql = "ALTER TABLE corecmsuser ADD cardNo varchar(50);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsuser", "cardId"))
            {
                msql = "ALTER TABLE corecmsuser ADD cardId varchar(50);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsuser", "cardTypeId"))
            {
                msql = "ALTER TABLE corecmsuser ADD cardTypeId varchar(50);";
                ExecuteCommand(msql);
            }
            Create_syssetting();
            if (!CheckColumn("corecmsfoodordermaster", "userOldPoint"))
            {
                msql = "ALTER TABLE corecmsfoodordermaster ADD userOldPoint int;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfoodordermaster ADD userOldBalance decimal(10, 2);";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfoodordermaster ADD userNewPoint int;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfoodordermaster ADD userNewBalance decimal(10, 2);";
                ExecuteCommand(msql);
            }
            if (CheckColumnType("corecmsfood", "helpCode", "varchar(20)"))
            {
                msql = "ALTER TABLE corecmsfood CHANGE COLUMN helpCode helpCode VARCHAR(200);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsexchange", "chgPriceId"))
            {
                msql = "ALTER TABLE corecmsexchange ADD chgPriceId bigint;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsexchange ADD chgPriceTime datetime;";
                ExecuteCommand(msql);
            }
            Create_corecmschgprice();
            Create_corecmsfoodbarcodes();
            if (!CheckColumn("corecmspayments", "tenantBalanceTypeId"))
            {
                msql = "ALTER TABLE corecmspayments ADD tenantBalanceTypeId bigint;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmspayments", "posUsed"))
            {
                msql = "ALTER TABLE corecmspayments ADD posUsed int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmspayments", "rechargeUsed"))
            {
                msql = "ALTER TABLE corecmspayments ADD rechargeUsed int;";
                ExecuteCommand(msql);
            }
            if (!CheckDataRow("SELECT * FROM `corecmspayments` where `code` = 'cash' and id = 1"))
            {
                msql = @"update `corecmspayments` set `code` = 'pay.cash' where id = 1;
                        update `corecmspayments` set `code` = 'pay.coupon' where id = 2;
                        update `corecmspayments` set `code` = 'pay.mix' where id = 3;
                        update `corecmspayments` set `code` = 'pay.weixin.micropay' where id = 4;
                        update `corecmspayments` set `code` = '006' where id = 6;
                        update `corecmspayments` set `code` = 'pay.vip' where id = 7;
                        update `corecmspayments` set `code` = 'pay.alipay.micropay' where id = 9;";
                ExecuteCommand(msql);
            }
            if (CheckDataRow("SELECT * FROM `corecmssystemsetup` where downBaseTimes < 100"))
            {
                msql = @"update `corecmssystemsetup` set `downBaseTimes` = 360;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsexchange", "branchPriceId"))
            {
                msql = "ALTER TABLE corecmsexchange ADD branchPriceId bigint;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsexchange ADD branchPriceTime datetime;";
                ExecuteCommand(msql);
            }
            Create_corecmsfoodprice();
            if (CheckColumnType("corecmsfoodprice", "disItem", "varchar(255)"))
            {
                msql = "ALTER TABLE corecmsfoodprice CHANGE COLUMN disItem disItem int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodprice", "batchId"))
            {
                msql = "ALTER TABLE corecmsfoodprice ADD batchId bigint;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodcategory", "tenantId"))
            {
                msql = "ALTER TABLE corecmsfoodcategory ADD tenantId bigint;";
                ExecuteCommand(msql);
                msql = @"update `corecmsfoodcategory` set `tenantId` = corpId;";
                ExecuteCommand(msql);
                msql = @"update `corecmsexchange` set foodCategoryTime = '2021-01-02 01:01:01';";
                ExecuteCommand(msql);
            }
            Create_corecmsfoodunit();
            if (!CheckColumn("corecmsexchange", "foodUnitId"))
            {
                msql = "ALTER TABLE corecmsexchange ADD foodUnitId bigint;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsexchange ADD foodUnitTime datetime;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfood", "parentTenantId"))
            {
                msql = "ALTER TABLE corecmsfood ADD parentTenantId bigint;";
                ExecuteCommand(msql);
                msql = @"update `corecmsfood` set `parentTenantId` = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsstore", "parentTenantId"))
            {
                msql = "ALTER TABLE corecmsstore ADD parentTenantId int;";
                ExecuteCommand(msql);
                msql = @"update `corecmsstore` set `parentTenantId` = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsexchange", "mallOrderTime"))
            {
                msql = "ALTER TABLE corecmsexchange ADD mallOrderTime datetime;";
                ExecuteCommand(msql);
                msql = @"update `corecmsexchange` set mallOrderTime = '2021-01-02 01:01:01';";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "checkTimes"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD checkTimes int;";
                ExecuteCommand(msql);
                msql = "Update corecmssystemsetup Set checkTimes = 0;";
                ExecuteCommand(msql);
            }
            Create_sysrecordentity();
            Create_corecmsordermaster();
            SetUpgradeVersion(sVersion);
        }

        private void Create_syssetting()
        {
            if (CheckTable("syssetting")) return;

            string msql = @"CREATE TABLE `syssetting`  (
                  `corpId` int NOT NULL,
                  `verifyPayPassword` tinyint NOT NULL,
                  `bonusSetting` int NULL DEFAULT NULL,
                  `bonusRatio` decimal NULL DEFAULT NULL,
                  PRIMARY KEY (`corpId`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_corecmsscaledetail()
        {
            if (CheckTable("corecmsscaledetail")) return;

            string msql = @"CREATE TABLE `corecmsscaledetail`  (
                  `id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                  `scaleId` bigint NULL DEFAULT NULL,
                  `foodSpecId` bigint NULL DEFAULT NULL,
                  PRIMARY KEY (`id`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_corecmschgprice()
        {
            if (CheckTable("corecmschgprice")) return;

            string msql = @"CREATE TABLE `corecmschgprice`  (
                  `chgpriceLineId` bigint NOT NULL,
                  `pdtSpuSpecId` bigint NULL DEFAULT NULL,
                  `newSalePrice` decimal(10, 2) NULL DEFAULT NULL,
                  `billNo` varchar(200) CHARACTER SET armscii8 COLLATE armscii8_bin NULL DEFAULT NULL,
                  `status` int NULL DEFAULT NULL,
                  `effectiveDate` datetime NULL DEFAULT NULL,
                  `expiringDate` datetime NULL DEFAULT NULL,
                  `timeType` int NULL DEFAULT NULL,
                  `updateTime` datetime NULL DEFAULT NULL,
                  PRIMARY KEY (`chgpriceLineId`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = armscii8 COLLATE = armscii8_bin ROW_FORMAT = Dynamic;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_corecmsfoodbarcodes()
        {
            if (CheckTable("corecmsfoodbarcodes")) return;

            string msql = @"CREATE TABLE `corecmsfoodbarcodes`  (
                  `barcodeId` bigint NOT NULL COMMENT 'ID',
                  `tenantId` int NULL DEFAULT NULL COMMENT '商户ID',
                  `barcode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '条码',
                  `spuId` bigint NULL DEFAULT NULL COMMENT '商品ID',
                  `deleteFlag` tinyint NULL DEFAULT NULL COMMENT '是否删除',
                  PRIMARY KEY (`barcodeId`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_corecmsfoodprice()
        {
            if (CheckTable("corecmsfoodprice")) return;

            string msql = @"CREATE TABLE `corecmsfoodprice`  (
                  `pdtSpuSpecId` bigint NOT NULL COMMENT '产品规格ID',
                  `saPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '标准零售价(价格表)',
                  `specChgPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '规格变更价格（允许前台改价 0 是 1 否）',
                  `specSaPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '标准零售价（商品档案 ）',
                  `specTenantId` int NULL DEFAULT NULL COMMENT '商户ID（商品档案)',
                  `specVipPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '售价2（商品档案 ）',
                  `specVvipPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '售价3（商品档案 ）',
                  `stopSaleFlag` tinyint NULL DEFAULT NULL COMMENT '是否停售0是 1否',
                  `tenantId` int NULL DEFAULT NULL COMMENT '商户ID(分店)',
                  `vipPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '售价2',
                  `vvipPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '售价3',
                  `disItem` int NULL DEFAULT NULL COMMENT '折扣项',
                  `maxPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '最高售价',
                  `minPrice` decimal(10, 2) NULL DEFAULT NULL COMMENT '最底售价',
                  PRIMARY KEY (`pdtSpuSpecId`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_corecmsfoodunit()
        {
            if (CheckTable("corecmsfoodunit")) return;

            string msql = @"CREATE TABLE `corecmsfoodunit`  (
                  `uomId` bigint NOT NULL COMMENT '单位Id',
                  `tenantId` int NULL DEFAULT NULL COMMENT '商户号',
                  `uomCode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '单位编码',
                  `uomName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '单位名称',
                  `converWay` int NULL DEFAULT NULL COMMENT '换算方式 0 固定换算1浮动换算',
                  `converRate` decimal(14, 4) NULL DEFAULT NULL COMMENT '换算率',
                  `canDefault` int NULL DEFAULT NULL COMMENT '是否默认 0 是 1 否',
                  `delFlag` int NULL DEFAULT NULL COMMENT '是否删除 0 未删除 1 已删除',
                  `stand` int NULL DEFAULT NULL COMMENT '是否称重',
                  `enable` int NULL DEFAULT NULL COMMENT '是否有效 0 有效 1失效',
                  `createTime` datetime NULL DEFAULT NULL COMMENT '创建时间',
                  `updateTime` datetime NULL DEFAULT NULL COMMENT '更新时间',
                  PRIMARY KEY (`uomId`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_sysrecordentity()
        {
            if (CheckTable("sysrecordentity")) return;

            string msql = @"CREATE TABLE `sysrecordentity`  (
                  `id` bigint NOT NULL COMMENT '主键id',
                  `tenantId` int NULL DEFAULT NULL COMMENT '商户id',
                  `recordName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '数据表名',
                  `recordTime` datetime NULL DEFAULT NULL COMMENT '更新时间',
                  PRIMARY KEY (`id`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_corecmsscreenimg()
        {
            if (CheckTable("corecmsscreenimg")) return;

            string msql = @"CREATE TABLE `corecmsscreenimg`  (
                  `tenantScreenImgId` bigint NOT NULL COMMENT '主键id',
                  `tenantScreenId` bigint NULL DEFAULT NULL COMMENT '图片id',
                  `imgUrl` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '数据表名',
                  PRIMARY KEY (`tenantScreenImgId`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_sysroleinfo()
        {
            if (CheckTable("sysroleinfo")) return;

            string msql = @"CREATE TABLE `sysroleinfo`  (
                  `roleId` bigint NOT NULL,
                  `roleName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                  `roleKey` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                  `maxDiscountAmount` decimal(12, 4) NULL DEFAULT NULL,
                  `discountRate` decimal(12, 4) NULL DEFAULT NULL,
                  `status` tinyint NULL DEFAULT NULL,
                  `delFlag` int NULL DEFAULT NULL,
                  `updateTime` datetime NULL DEFAULT NULL,
                  `createTime` datetime NULL DEFAULT NULL,
                  PRIMARY KEY (`roleId`) USING BTREE
                ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;";
            try
            {
                ExecuteCommand(msql);
                msql = "update `sysuser` set `passWord` = ''";
                ExecuteCommand(msql);
            }
            catch { }
        }

        private void Create_corecmsordermaster()
        {
            if (CheckTable("corecmsordermaster")) return;

            string msql = @"CREATE TABLE `corecmsordermaster`  (
                      `id` bigint NOT NULL,
                      `tenantId` int NULL DEFAULT NULL COMMENT '商户id',
                      `orderSn` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '订单号',
                      `payAmount` decimal(14, 2) NULL DEFAULT NULL COMMENT '订单号',
                      `totalAmount` decimal(14, 2) NULL DEFAULT NULL COMMENT '支付总金额',
                      `userName` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '订单总金额',
                      `userPhone` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名称',
                      `payTime` datetime NULL DEFAULT NULL COMMENT '用户手机号',
                      `status` int NULL DEFAULT NULL COMMENT '订单状态',
                      `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '内容',
                      `checkFlag` tinyint NULL DEFAULT NULL COMMENT '已读',
                      PRIMARY KEY (`id`) USING BTREE
                    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;";
            try
            {
                ExecuteCommand(msql);
            }
            catch { }
        }

        #endregion

        /// <summary>
        /// 自动升级数据库
        /// </summary>
        public void SetDataUpgrade()
        {
            int oldVersion = GetUpgradeVersion();
            int newVersion = 250812;
            if (oldVersion < newVersion) SetDataUpgrade_250621(newVersion);
            if (oldVersion < newVersion) SetDataUpgrade_250812(newVersion);            
            string msql;           
            
            if (!CheckColumn("corecmssystemsetup", "checkTimes"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD checkTimes int;";
                ExecuteCommand(msql);
                msql = "Update corecmssystemsetup Set checkTimes = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfood", "place"))
            {
                msql = "ALTER TABLE corecmsfood ADD place VARCHAR(50);";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfood ADD periodDay int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodspec", "spuTypeId"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD spuTypeId int;";
                ExecuteCommand(msql);
                msql = "Update corecmsfoodspec Set spuTypeId = 1;";
                ExecuteCommand(msql);
            }
            if (CheckColumnType("corecmsuserpoint", "storeId", "int"))
            {
                msql = "ALTER TABLE corecmsuserpoint CHANGE COLUMN storeId storeId bigint;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("activitymaster", "weekdayType"))
            {
                msql = "ALTER TABLE activitymaster ADD weekdayType int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("activitymaster", "activityBeginTime"))
            {
                msql = "ALTER TABLE activitymaster ADD activityBeginTime time;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE activitymaster ADD activityEndTime time;";
                ExecuteCommand(msql);
            }
            if (CheckColumnType("corecmsfood", "image", "varchar(200)"))
            {
                msql = "ALTER TABLE corecmsfood CHANGE COLUMN image image VARCHAR(1000);";
                ExecuteCommand(msql);
            }
            if (CheckDataRow("SELECT * FROM `corecmspayments` where `code` = '006'"))
            {
                msql = @"update `corecmspayments` set `code` = 'pay.integral' where id = 6;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "showFoodImgCust"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD showFoodImgCust tinyint;";
                ExecuteCommand(msql);
                msql = @"update `corecmssystemsetup` set `showFoodImgCust` = 1;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsuserbalance", "direction"))
            {
                msql = "ALTER TABLE corecmsuserbalance ADD direction int;";
                ExecuteCommand(msql);
                msql = @"update `corecmsuserbalance` set `direction` = 1;";
                ExecuteCommand(msql);
                msql = @"update `corecmsuserbalance` set `direction` = -1 where type = 1;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodspec", "chgPrice"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD chgPrice tinyint;";
                ExecuteCommand(msql);
                msql = @"update `corecmsfoodspec` set `chgPrice` = 1;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfoodspec ADD disItem tinyint;";
                ExecuteCommand(msql);
                msql = @"update `corecmsfoodspec` set `disItem` = 1;";
                ExecuteCommand(msql);
            }
            if (CheckColumnType("corecmsfoodordering", "quantity", "decimal(10,2)") || CheckColumnNumeric("corecmsfoodordering", "quantity", 2))
            {
                msql = "ALTER TABLE corecmsfoodordering CHANGE COLUMN quantity quantity decimal(10, 3);";
                ExecuteCommand(msql);
            }
            if (CheckColumnType("corecmsfoodorderdetail", "quantity", "decimal(12,2)") || CheckColumnNumeric("corecmsfoodorderdetail", "quantity", 2))
            {
                msql = "ALTER TABLE corecmsfoodorderdetail CHANGE COLUMN quantity quantity decimal(12, 3);";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("sysuser", "posRoleId"))
            {
                msql = "ALTER TABLE sysuser ADD posRoleId bigint;";
                ExecuteCommand(msql);
            }
            Create_corecmsscreenimg();
            Create_sysroleinfo();
            if (!CheckColumn("corecmsfoodspec", "maxPrice"))
            {
                msql = "ALTER TABLE corecmsfoodspec ADD maxPrice decimal(12,4);";
                ExecuteCommand(msql);
                msql = @"update `corecmsfoodspec` set `maxPrice` = 0;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfoodspec ADD minPrice decimal(12,4);";
                ExecuteCommand(msql);
                msql = @"update `corecmsfoodspec` set `minPrice` = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordering", "maxPrice"))
            {
                msql = "ALTER TABLE corecmsfoodordering ADD maxPrice decimal(12,4);";
                ExecuteCommand(msql);
                msql = @"update `corecmsfoodordering` set `maxPrice` = 0;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfoodordering ADD minPrice decimal(12,4);";
                ExecuteCommand(msql);
                msql = @"update `corecmsfoodordering` set `minPrice` = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "prinMethod"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD prinMethod int;";
                ExecuteCommand(msql);
                msql = @"update `corecmssystemsetup` set `prinMethod` = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfood", "stand"))
            {
                msql = "ALTER TABLE corecmsfood ADD stand int;";
                ExecuteCommand(msql);
                msql = @"UPDATE corecmsfood e JOIN corecmsfoodspec p ON e.foodSpecId = p.id SET e.stand = 1 where p.stand = 0;";
                ExecuteCommand(msql);
            }
            if (CheckColumn("corecmsfoodspec", "stand"))
            {
                msql = @"update `corecmsfoodspec` set `weighType` = `stand`;";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsfoodspec DROP COLUMN stand;";
                ExecuteCommand(msql);
                msql = @"UPDATE corecmsfood e JOIN corecmsfoodspec p ON e.foodSpecId = p.id SET e.stand = 1 where p.weighType = 1;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordering", "disItem"))
            {
                msql = "ALTER TABLE corecmsfoodordering ADD disItem tinyint;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsprinttaskdetail", "unitName"))
            {
                msql = "ALTER TABLE corecmsprinttaskdetail ADD unitName  varchar(100);";
                ExecuteCommand(msql);
                msql = "ALTER TABLE corecmsprinttaskdetail ADD itemCount int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodordering", "periodDay"))
            {
                msql = "ALTER TABLE corecmsfoodordering ADD periodDay int;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("activitylineresp", "activitySubTypeId"))
            {
                msql = "ALTER TABLE activitylineresp ADD activitySubTypeId bigint;";
                ExecuteCommand(msql);
                msql = @"UPDATE activitylineresp e JOIN activitymaster p ON e.activityInfoId = p.activityInfoId SET e.activitySubTypeId = p.activitySubTypeId;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmspayments", "content"))
            {
                msql = "ALTER TABLE corecmspayments ADD content text;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmspayments", "symbol"))
            {
                msql = "ALTER TABLE corecmspayments ADD symbol int;";
                ExecuteCommand(msql);
                msql = @"Update corecmspayments set symbol = 157471,posUsed = 1,rechargeUsed = 1 where id = 1;
                        Update corecmspayments set sort = id;
                        Update corecmspayments set symbol = 157473 where id = 3;
                        Update corecmspayments set symbol = 161803 where id = 4;
                        Update corecmspayments set symbol = 261447 where id = 7;
                        Update corecmspayments set symbol = 158426 where id = 9;
                        Update corecmspayments set symbol = 161929 where id = 10;
                        Update corecmspayments set symbol = 163543 where id = 11;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsordermaster", "isUpload"))
            {
                msql = "ALTER TABLE corecmsordermaster ADD isUpload tinyint;";
                ExecuteCommand(msql);
                msql = @"UPDATE corecmsordermaster SET isUpload = 1;";
                ExecuteCommand(msql);
            }
            if (CheckColumnType("corecmsprintbilltemplate", "reportData", "text"))
            {
                msql = "ALTER TABLE corecmsprintbilltemplate CHANGE COLUMN reportData reportData longtext;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsuser", "sumBalance"))
            {
                msql = "ALTER TABLE corecmsuser ADD sumBalance decimal(12, 2);";
                ExecuteCommand(msql);
                msql = @"UPDATE corecmsuser SET sumBalance = 0;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodorderpayment", "outcomeType"))
            {
                msql = "ALTER TABLE corecmsfoodorderpayment ADD outcomeType int;";
                ExecuteCommand(msql);
                msql = @"UPDATE corecmsfoodorderpayment SET outcomeType = 1;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsfoodcategory", "canPos"))
            {
                msql = @"ALTER TABLE corecmsfoodcategory ADD canPos tinyint;
                        ALTER TABLE corecmsfoodcategory ADD canParentPos tinyint;";
                ExecuteCommand(msql);
                msql = @"UPDATE corecmsfoodcategory SET canPos = 1,canParentPos = 1;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmssystemsetup", "foodTypeBindType"))
            {
                msql = "ALTER TABLE corecmssystemsetup ADD foodTypeBindType int;";
                ExecuteCommand(msql);
                msql = @"UPDATE corecmssystemsetup SET foodTypeBindType = 0;";
                ExecuteCommand(msql);
            }
            Create_corecmsscaledetail();
            if (CheckDataRow("SELECT * FROM `corecmssystemsetup` where (billNumberFirst <> 3)  or (billNumberLast <> 4)"))
            {
                msql = @"update `corecmssystemsetup` set `billNumberFirst` = 3,billNumberLast = 4;";
                ExecuteCommand(msql);
            }
            if (!CheckColumn("corecmsuserbalance", "orderNo"))
            {
                msql = "ALTER TABLE corecmsuserbalance ADD orderNo varchar(50);";
                ExecuteCommand(msql);
            }
        }

        public void ExecuteCommand(string msql)
        {
            try
            {
                var db = new sysuserServices().GetInstance();
                db.Ado.ExecuteCommand(msql);
            }
            catch { }
        }

        private bool CheckTable(string tableName)
        {
            var para = new List<SugarParameter>()
            {
                new SugarParameter("@database_name", "youzi_pos"),
                new SugarParameter("@table_name", tableName),
            };            

            string msql = "SELECT table_name FROM information_schema.tables WHERE table_schema = @database_name AND table_name = @table_name;";
            var db = new sysuserServices().GetInstance();
            var dt = db.Ado.GetDataTable(msql, para);
            if ((dt != null) && (dt.Rows.Count > 0)) return true;
            return false;
        }

        private bool CheckDataRow(string msql)
        {            
            var db = new sysuserServices().GetInstance();
            var dt = db.Ado.GetDataTable(msql);
            if ((dt != null) && (dt.Rows.Count > 0)) return true;
            return false;
        }

        private bool CheckColumn(string tableName, string columnName)
        {
            var para = new List<SugarParameter>()
            {
                new SugarParameter("@tableName", tableName),
                new SugarParameter("@columnName", columnName),
            };
            string msql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS "
                + "WHERE Table_SCHEMA ='youzi_pos' and table_name = @tableName AND column_name = @columnName";
            var db = new sysuserServices().GetInstance();
            var dt = db.Ado.GetDataTable(msql, para);
            if ((dt != null) && (dt.Rows.Count > 0)) return true;
            return false;
        }

        private bool CheckColumnType(string tableName, string columnName, string columnType)
        {
            var para = new List<SugarParameter>()
            {
                new SugarParameter("@tableName", tableName),
                new SugarParameter("@columnName", columnName),
                new SugarParameter("@columnType", columnType),
            };
            string msql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS "
                + "WHERE Table_SCHEMA ='youzi_pos' and table_name = @tableName AND column_name = @columnName and column_type = @columnType";
            var db = new sysuserServices().GetInstance();
            var dt = db.Ado.GetDataTable(msql, para);
            if ((dt != null) && (dt.Rows.Count > 0)) return true;
            return false;
        }

        private bool CheckColumnNumeric(string tableName, string columnName, int Numeric_scale)
        {
            var para = new List<SugarParameter>()
            {
                new SugarParameter("@tableName", tableName),
                new SugarParameter("@columnName", columnName),
                new SugarParameter("@Numeric_scale", Numeric_scale),
            };
            string msql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS "
                + "WHERE Table_SCHEMA ='youzi_pos' and table_name = @tableName AND column_name = @columnName and NUMERIC_SCALE = @Numeric_scale";
            var db = new sysuserServices().GetInstance();
            var dt = db.Ado.GetDataTable(msql, para);
            if ((dt != null) && (dt.Rows.Count > 0)) return true;
            return false;
        }

    }
}
